<?php
 
/*
 * Following code will list all the place
 */
 
// array for JSON response
$response = array();

// calculate geographical proximity
function mathGeoProximity( $latitude, $longitude, $radius, $miles = false )
{
    $radius = $miles ? $radius : ($radius * 0.621371192);

    $lng_min = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
    $lng_max = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
    $lat_min = $latitude - ($radius / 69);
    $lat_max = $latitude + ($radius / 69);

    return array(
        'latitudeMin'  => $lat_min,
        'latitudeMax'  => $lat_max,
        'longitudeMin' => $lng_min,
        'longitudeMax' => $lng_max
    );
}

// calculate geographical distance between 2 points
function mathGeoDistance( $lat1, $lng1, $lat2, $lng2, $miles = false )
{
    $pi80 = M_PI / 180;
    $lat1 *= $pi80;
    $lng1 *= $pi80;
    $lat2 *= $pi80;
    $lng2 *= $pi80;

    $r = 6372.797; // mean radius of Earth in km
    $dlat = $lat2 - $lat1;
    $dlng = $lng2 - $lng1;
    $a = sin($dlat / 2) * sin($dlat / 2) + cos($lat1) * cos($lat2) * sin($dlng / 2) * sin($dlng / 2);
    $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
    $km = $r * $c;

    return ($miles ? ($km * 0.621371192) : $km);
}
 
if (isset($_GET['sourceLat']) && isset($_GET['sourceLon']) && isset($_GET['radiusKm'])){
	$sourceLat = $_GET['sourceLat'];
	$sourceLon = $_GET['sourceLon'];
	$radiusKm = $_GET['radiusKm'];

	$proximity = mathGeoProximity($sourceLat, $sourceLon, $radiusKm);
	
	// include db connect class
	require_once __DIR__ . '/db_connect.php';
	 
	// connecting to db
	$db = new DB_CONNECT();
	 
	// get all place from place table

	$result = mysql_query("SELECT pl.place_id
								,pl.place_name
								,pl.address
								,case
									when lk.count_like is null or lk.count_like = '' then 0
									else lk.count_like
									end count_like
								,case
									when cm.count_comment is null or cm.count_comment = '' then 0
									else cm.count_comment
									end count_comment
								,case
									when rt.point_rating is null or rt.point_rating = '' then 0
									else rt.point_rating
									end point_rating
								,pl.url_avatar
								,pl.name_avatar
								,pl.longitude
								,pl.latitude
								,dt.name as district_name
								,pr.name as province_name
							FROM place as pl 	
								LEFT JOIN  
									(select place_id, FLOOR(sum(point)/count(id)) as point_rating
									 from  rating 
									 group by place_id) as rt
									on rt.place_id = pl.place_id
								LEFT JOIN
									(select place_id, count(id) as count_like
									 from  `like` 
									 group by place_id) as lk
									on lk.place_id = pl.place_id
								LEFT JOIN
									(select place_id, count(comment_id) as count_comment
									 from  `comment` 
									 group by place_id) as cm
									on cm.place_id = pl.place_id
								LEFT JOIN district dt
									ON pl.district_id = dt.id
								LEFT JOIN province pr
									ON dt.province_id = pr.id
							WHERE 
								(pl.latitude BETWEEN " . number_format($proximity['latitudeMin'], 12, '.', '') . "
										AND " . number_format($proximity['latitudeMax'], 12, '.', '') . ")
								  AND (pl.longitude BETWEEN " . number_format($proximity['longitudeMin'], 12, '.', '') . "
										AND " . number_format($proximity['longitudeMax'], 12, '.', '') . ")
							ORDER BY point_rating DESC
									,count_like DESC
									,count_comment DESC
									,pl.place_name DESC") or die(mysql_error());
	
	$recordsWithinRadius = array();
	while ($record = mysql_fetch_assoc($result)) {
		$distance = mathGeoDistance($sourceLat, $sourceLon, $record['latitude'], $record['longitude']);
		
		if ($distance <= $radiusKm) {
			$recordsWithinRadius[] = $record;
			//echo json_encode($recordsWithinRadius);
		}
	}
	
	// check for empty result
	if (count($recordsWithinRadius) > 0) {
		// looping through all results
		// place node
		$response["place"] = array();
	 
		//while ($row = mysql_fetch_array($recordsWithinRadius)) 
		for($i = 0; $i < count($recordsWithinRadius); $i++)
		{
			// temp user array
			$place = array();    
			$place["place_id"] = $recordsWithinRadius[$i]["place_id"];
			$place["place_name"] = $recordsWithinRadius[$i]["place_name"];
			$place["address"] = $recordsWithinRadius[$i]["address"];	
			$place["district_name"] = $recordsWithinRadius[$i]["district_name"]; 
			$place["province_name"] = $recordsWithinRadius[$i]["province_name"]; 
			$place["longitude"] = $recordsWithinRadius[$i]["longitude"]; 
			$place["latitude"] = $recordsWithinRadius[$i]["latitude"]; 
			$place["url_avatar"] = $recordsWithinRadius[$i]["url_avatar"];	
			$place["name_avatar"] = $recordsWithinRadius[$i]["name_avatar"];	
			$place["count_like"] = $recordsWithinRadius[$i]['count_like'];
			$place["count_comment"] = $recordsWithinRadius[$i]['count_comment'];
			$place["point_rating"] = $recordsWithinRadius[$i]['point_rating'];
		
			// push single product into final response array
			
			array_push($response["place"], $place);			
			//echo json_encode($recordsWithinRadius[$i]);
		}
		// success
		$response["success"] = 1;
	 
		// echoing JSON response
		echo json_encode($response["place"]);
	} else {
		// no place found
		$response["success"] = 0;
		$response["message"] = "No place found";
	 
		// echo no users JSON
		echo json_encode($response);
	}
} else {
    // required field is missing
    $response["success"] = 0;
    $response["message"] = "Required field(s) is missing";
 
    // echoing JSON response
    echo json_encode($response);
}

?>